package dao.mysql;

import java.util.ArrayList;
import java.sql.*;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import model.BookSearchCriteria;
import model.BookingDTO;
import model.FacilityType;
import model.RequestBooking;
import dao.BookingDAO;

public class BookDAOImpl implements BookingDAO{
	DataSource dataSource=null;
	Connection con=null;
	PreparedStatement pstmt=null;
	
	public BookDAOImpl()
	{
		try{
			//dataSource=(DataSource) new InitialContext().lookup("java:comp/env/jdbc/fbs");
			//Class.forName("com.mysql.jdbc.Driver");
			//con=dataSource.getConnection();
			con=ConnectionProperties.getConnection();
			}
			catch(Exception ee){
			System.out.println(ee.getMessage());
			}
		
	}
	@Override
	
	public void insertBooking(BookingDTO book) throws SQLException {
		// TODO Auto-generated method stub
		try
		{
			
			
			if(con.isClosed())
			{
				con=ConnectionProperties.getConnection();
			}
			pstmt=con.prepareStatement("insert into booking(facilityname,staffid,status,fromdate,todate,priorityid,bookingdate,reason,LastModified) values(?,?,?,?,?,?,?,?,now())");
			//pstmt.setInt(1, book.getBookingID());
			System.out.println(book);
			pstmt.setString(1, book.getFacilityName());
			pstmt.setString(2, book.getStaffID());
			pstmt.setString(3, book.getStatus());
			pstmt.setDate(4, book.getFromDate());
			pstmt.setDate(5, book.getToDate());
			pstmt.setInt(6,book.getPriorityID());
			pstmt.setDate(7, book.getBookingDate());
			pstmt.setString(8, book.getReason());
			pstmt.executeUpdate();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			con.close();
			pstmt.close();
		}
	}
	//Gaday coding
		@Override
		public BookingDTO findByBookingID(String BookingID) {
			// TODO Auto-generated method stub
			BookingDTO book=null;
			try {
				pstmt=con.prepareStatement("select * from Booking where BookingID=?");
				pstmt.setString(1, BookingID);
				ResultSet rs=pstmt.executeQuery();
				if(rs.next())
				{
					book=new BookingDTO();
					book.setBookingID(Integer.parseInt(rs.getString("BookingID")));
					book.setFacilityName(rs.getString("FacilityName"));
					book.setStaffID(rs.getString("StaffID"));
					book.setFromDate(rs.getDate("FromDate"));
					book.setToDate(rs.getDate("ToDate"));
					book.setPriorityID(Integer.parseInt((rs.getString("PriorityID"))));
					book.setStatus(rs.getString("Status"));
					book.setBookingDate(rs.getDate("BookingDate"));
					book.setReason(rs.getString("Reason"));
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally
			{
				try {
					con.close();
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}				
			}
			return book;
		}
	//Gaday end
	@Override
	public int updateBooking(int BookingID,String status) throws SQLException {
		// TODO Auto-generated method stub
		int result=0;
		if(con.isClosed())
		{
			con=ConnectionProperties.getConnection();
		}
		try {
			pstmt=con.prepareStatement("update booking set status=? where bookingid=?");
			pstmt.setInt(2, BookingID);
			pstmt.setString(1, status);
			result=pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			con.close();
			pstmt.close();
		}
		return result;
	}

	@Override
	public void deleteBooking(int BookingID) throws SQLException {
		// TODO Auto-generated method stub
		try {
			if(con.isClosed())
			{
				con=ConnectionProperties.getConnection();
			}
			pstmt=con.prepareStatement("delete from Booking where BookingID=?");
			pstmt.setInt(1, BookingID);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			con.close();
			pstmt.close();
		}
	}

	@Override
	public BookingDTO findByBookingID(int BookingID) throws SQLException {
		// TODO Auto-generated method stub
		BookingDTO book=null;
		try {
			if(con.isClosed())
			{
				con=ConnectionProperties.getConnection();
			}
			pstmt=con.prepareStatement("select * from Booking where BookingID=?");
			pstmt.setInt(1, BookingID);
			ResultSet rs=pstmt.executeQuery();
			if(rs.next())
			{
				book=new BookingDTO();
				book.setBookingID(rs.getInt("BookingID"));
				book.setFacilityName(rs.getString("FacilityName"));
				book.setStaffID(rs.getString("StaffID"));
				book.setFromDate(rs.getDate("FromDate"));
				book.setToDate(rs.getDate("ToDate"));
				book.setPriorityID(rs.getInt("PriorityID"));
				book.setStatus(rs.getString("Status"));
				book.setBookingDate(rs.getDate("BookingDate"));
				book.setReason(rs.getString("Reason"));
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			con.close();
			pstmt.close();
		}
		return book;
	}

	@Override
	public ArrayList<BookingDTO> findAllBooking() throws SQLException {
		// TODO Auto-generated method stub
		ArrayList<BookingDTO> books=new ArrayList<BookingDTO>();
		try {
			if(con.isClosed())
			{
				con=ConnectionProperties.getConnection();
			}
			pstmt=con.prepareStatement("select * from Booking");
			ResultSet rs=pstmt.executeQuery();
			while(rs.next())
			{
				BookingDTO book=new BookingDTO();
				book.setBookingID(rs.getInt("BookingID"));
				book.setFacilityName(rs.getString("FacilityName"));
				book.setStaffID(rs.getString("StaffID"));
				book.setFromDate(rs.getDate("FromDate"));
				book.setToDate(rs.getDate("ToDate"));
				book.setPriorityID(rs.getInt("PriorityID"));
				book.setStatus(rs.getString("Status"));
				book.setBookingDate(rs.getDate("BookingDate"));
				book.setReason(rs.getString("Reason"));
				books.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			con.close();
			pstmt.close();
		}
		return books;
	}
	public ArrayList<BookingDTO> findByCriteria(BookSearchCriteria criteria)
	{
		ArrayList<BookingDTO> booking=null;
		if(criteria.getFacilityName()!=null)
		{
			booking=new ArrayList<BookingDTO>();
			try {
				if(con.isClosed())
				{
					con=ConnectionProperties.getConnection();
				}
				pstmt=con.prepareStatement("select * from Booking where FacilityName=? and month(FromDate)=?");
				pstmt.setString(1, criteria.getFacilityName());
				pstmt.setInt(2, criteria.getMonth());
				ResultSet rs=pstmt.executeQuery();
				while(rs.next())
				{
					BookingDTO book=new BookingDTO();
					book.setBookingID(rs.getInt("BookingID"));
					book.setFacilityName(rs.getString("FacilityName"));
					book.setStaffID(rs.getString("StaffID"));
					book.setFromDate(rs.getDate("FromDate"));
					book.setToDate(rs.getDate("ToDate"));
					book.setPriorityID(rs.getInt("PriorityID"));
					book.setStatus(rs.getString("Status"));
					book.setBookingDate(rs.getDate("BookingDate"));
					book.setReason(rs.getString("Reason"));
					booking.add(book);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally
			{
				try {
					con.close();
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
		}
		else
		{
		if(criteria.getStaffID()!=null)
		{
			booking=new ArrayList<BookingDTO>();
			try {
				if(con.isClosed())
				{
					con=ConnectionProperties.getConnection();
				}
				pstmt=con.prepareStatement("select * from Booking where StaffID=?");
				pstmt.setString(1, criteria.getStaffID());
				ResultSet rs=pstmt.executeQuery();
				while(rs.next())
				{
					BookingDTO book=new BookingDTO();
					book.setBookingID(rs.getInt("BookingID"));
					book.setFacilityName(rs.getString("FacilityName"));
					book.setStaffID(rs.getString("StaffID"));
					book.setFromDate(rs.getDate("FromDate"));
					book.setToDate(rs.getDate("ToDate"));
					book.setPriorityID(rs.getInt("PriorityID"));
					book.setStatus(rs.getString("Status"));
					book.setBookingDate(rs.getDate("BookingDate"));
					book.setReason(rs.getString("Reason"));
					booking.add(book);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally
			{
				try {
					con.close();
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
			
		}
		}
		return booking;
	}
	@Override
	public ArrayList<BookingDTO> findCurrentBookings() {
		ArrayList<BookingDTO> items = new ArrayList<BookingDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String selectSql = "SELECT * FROM booking WHERE YEAR(FromDate) = YEAR(CURDATE()) AND MONTH(FromDate) = MONTH(CURDATE());";
		
		try {
			if(con.isClosed())
			{
				con=ConnectionProperties.getConnection();
			}
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(selectSql);
			
			while (rs.next()) {
				BookingDTO booking = new BookingDTO();
				booking.setBookingID(rs.getInt("BookingID"));
				booking.setFacilityName(rs.getString("FacilityName"));
				booking.setStaffID(rs.getString("StaffID"));
				booking.setStatus(rs.getString("Status"));
				booking.setFromDate(rs.getDate("FromDate"));
				booking.setToDate(rs.getDate("ToDate"));
				booking.setPriorityID(rs.getInt("PriorityID"));
				booking.setBookingDate(rs.getDate("BookingDate"));
				booking.setReason(rs.getString("Reason"));

				items.add(booking);
			}
			st.close();
		} 
		
		catch (Exception e) {
			e.printStackTrace();
		} 
		
		finally {
			try {
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return items;
	}
	@Override
	public int findNumberOfBooking(String StaffID, int Month) {
		// TODO Auto-generated method stub
		int record=0;
		try {
			if(con.isClosed())
			{
				con=ConnectionProperties.getConnection();
			}
			pstmt=con.prepareStatement("select count(*) as bookCount from Booking where StaffID=? and month(FromDate)=? and PriorityID=3 and Status!='Cancel'");
			pstmt.setString(1, StaffID);
			pstmt.setInt(2, Month);
			ResultSet rs=pstmt.executeQuery();
			if(rs.next())
				record=rs.getInt("bookCount");
			System.out.println(record);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return record;
	}
	//Gaday coding
		@Override
		public ArrayList<RequestBooking> findPendingBooking() {
			// TODO Auto-generated method stub
			ArrayList<RequestBooking> books=new ArrayList<RequestBooking>();
			
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			
		
			try {
				
				Statement st = con.createStatement();
				ResultSet rs = st.executeQuery("select b.BookingID,b.FacilityName,b.FromDate,b.ToDate,p.PriorityType from booking b,priority p where b.PriorityID=p.PriorityID and b.status='Pending';");
				
				while(rs.next())
				{
					RequestBooking rb = new RequestBooking();
					rb.setBookingID(rs.getString("BookingID"));
					rb.setFacilityName(rs.getString("FacilityName"));
					//book.setStaffID(rs.getString("StaffID"));
					rb.setFromDate(rs.getDate("FromDate"));
					rb.setToDate(rs.getDate("ToDate"));
					rb.setPriorityType(rs.getString("PriorityType"));
					//book.setStatus(rs.getString("Status"));
					//book.setBookingDate(rs.getDate("BookingDate"));
					//book.setReason(rs.getString("Reason"));
					books.add(rb);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally
			{
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				//pstmt.close();
			}
			return books;
		}

	@Override
	public void updateBooking(BookingDTO booking) {
		// TODO Auto-generated method stub
		try {
			if(con.isClosed())
			{
				con=ConnectionProperties.getConnection();
			}
			pstmt=con.prepareStatement("update Booking set FacilityName=?,StaffID=?,Status=?,FromDate=?,ToDate=?,PriorityID=?,BookingDate=now(),Reason=? where BookingID=?");
			pstmt.setString(1, booking.getFacilityName());
			pstmt.setString(2, booking.getStaffID());
			pstmt.setString(3, booking.getStatus());
			pstmt.setDate(4, booking.getFromDate());
			pstmt.setDate(5, booking.getToDate());
			pstmt.setInt(6, booking.getPriorityID());
			pstmt.setString(7, booking.getReason());
			pstmt.setInt(8, booking.getBookingID());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				con.close();
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
	}
	
	public ArrayList<BookingDTO> ViewMonthlyUsage(Date date1,Date date2) throws SQLException{
		ArrayList<BookingDTO> books=new ArrayList<BookingDTO>();
		try {
			pstmt=con.prepareStatement("select f.facilityname,fac.facilitytype,sum(duration) as duration from booking b,facility f,facilitytype fac where b.facilityname=f.facilityname and f.facilitytypeid=fac.facilitytypeid and status='Approved' and b.fromdate BETWEEN ? AND ? group by facilityname,fac.facilitytype;");
			pstmt.setDate(1,date1);
			pstmt.setDate(2,date2);
			ResultSet rs=pstmt.executeQuery();
			while(rs.next())
			{
				BookingDTO book=new BookingDTO();
				FacilityType ft=new FacilityType();
				ft.setFacilityType(rs.getString("FacilityType"));
				book.setFacilityType(ft);
				book.setFacilityName(rs.getString("FacilityName"));
				book.setDuration(rs.getInt("Duration"));
				books.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			con.close();
			pstmt.close();
		}
		return books; 
	}
	
	public ArrayList<BookingDTO> ViewbyStaff(String staffID) throws SQLException{
		ArrayList<BookingDTO> books=new ArrayList<BookingDTO>();
		try {
			pstmt=con.prepareStatement("select b.facilityname,fac.facilitytype,b.duration from booking b,facility f,facilitytype fac where staffid=? and status='Approved' and f.facilityname= b.facilityname and f.facilitytypeid=fac.facilitytypeid;");
			pstmt.setString(1,staffID);
			ResultSet rs=pstmt.executeQuery();
			while(rs.next())
			{
				BookingDTO book=new BookingDTO();
				FacilityType ft=new FacilityType();
				ft.setFacilityType(rs.getString("FacilityType"));
				book.setFacilityType(ft);
				book.setFacilityName(rs.getString("FacilityName"));
				book.setDuration(rs.getInt("Duration"));
				books.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			con.close();
			pstmt.close();
		}
		return books; 
	}

	

}
